# !/usr/bin/python
# -*- coding: utf-8 -*-

# mediaplex
# multimedia platform
#
# this file is licensed under the MIT license
# read "LICENSE.MIT" in the root of this distribution

__authors__ = 'Jan Jokela <janjokela@gmail.com>'
__description__ = 'Media library database backends'

import pysqlite2.dbapi2

class Backend:
    """ Backend provides a pseudo-interface for database backends.

    Though it has a couple of lines of code for some trivial
    implementations, the class should mostly be regarded as
    an interface for the inheriting classes to folow.

    """
    def __init__(self, database_name='media.db', host='127.0.0.1', username='anonymous', password='password'):
        """ Initialize the database procedures.

        Gather some generally useful information, enabling the
        database "works" to flow transparently. It will also
        verify the integrity of the database and rebuild it
        completely if needed. This implementation is fundamented
        on the possibility of creating different backends for
        information storage (databases, filesystem, memory, ...).

        :host (string) -- The host the client should connect to.
        :username (string) -- The username used to connect to the database.
        :password (string) -- The password used to connect to the database.

        """
        self.host = host
        self.username = username
        self.password = password

    def add_record(self, table, some_object):
        """Add a record to the storage system."""
        pass

    def rem_record(self, table, field, value):
        """Remove a record from the storage system."""
        pass

    def change_record(self, old_object, new_object):
        """Alter a record from the storage system."""
        pass

    def get_records(self, table, fields_list):
        """Get the records present in the storage system."""
        pass

    def _connect(self, database_name):
        """Connect to the storage system."""
        pass

    def _disconnect(self):
        """Close the connection between the client and the storage system."""
        pass

    def _verify_integrity(self):
        """ Verify the integrity of the storage system.

        Check if everything is in place and return either True or
        False, representing the current state of the storage system.
        Usually, only the broad structure should be checked and not
        the contents.
        """
        pass

    def _rebuild_storage_system(self):
        """ Rebuilds the database.

        This is more of a failsafe method in case something severe
        happens, than something that is meant to be used with any
        kind of frequence.

        It will recreate all the Database structure (view above),
        which consists of: a database, with 3 tables (one for each
        type of media - audio, video and image), and each of those
        tables has specific fields for holding the metadata.
        """
        pass

    def _get_object_values(self, some_object):
        """ Converts a standard python object into a dictionary.

        This is for specific use, enable the user to pass an object, and
        have "it" inserted in the database. It's not the object 'per se',
        but we map all the attribute names to attribute values in a
        simple dictionary, and, further down the road, insert it into
        the database by converting the dictionary keys into the fields
        that the dictionary values will be inserted to.

        :some_object (object) -- The object to be converted into a dictionary.
        """
        fields = []
        values = []
        for field in dir(some_object):
            # XXX Verifying for "update" is implementation specific, but
            # I have no other way to elegantly prevent from returning
            # this specific method. A decent solution, would be to
            # verify if the attribute is a method and ditch it.
            if field.startswith('_') or field.startswith('update'):
                pass
            else:
                fields.append(field)
                values.append(getattr(some_object, field))
        return fields, values

class SQLite(Backend):
    """ The SQLite driver.

    Aside from blazingly fast (even if a very simplistic implementation),
    it strides from the most known databases by not using a client/server
    model for its implementation. Everything works on the local machine.
    It also has the added bonus of allowing the creation of a "virtual"
    database using the available memory (making the database operations
    even faster).
    """

    def __init__(self, database_name='media.db', host=None, username=None, password=None):
        Backend.__init__(self, database_name, host, username, password)
        self.database_name = 'mediadb'
        self.connection = None
        self.cursor = None
        self._connect(self.database_name)

    def add_record(self, table, some_object):
        """ Adds a record to the database.

        Though it's obvious the functionality of the method, the
        implementation has a few points to notice. First, it uses
        bound parameters (if you don't know what this is, googling
        won't harm) to prevent any kind of exploits. Second point
        to notice is the use of objects. It will attempt to retrieve
        the object's attributes and use them as reference for the
        columns where the values (the attributes' values) will be
        inserted into.

        :table (string) -- The table where the record will be inserted.
        :some_object (*) -- An object to convert and insert into the databse.
        """
        # Bound parameters: nice, easier, safer and more powerful
        fields, values = self._get_object_values(some_object)
        # Generate the columns that will be appointed for filling
        fields = ', '.join(fields)
        # Generates the exact number of '?' for matching
        bound_parameters = ', '.join(len(values)*['?'])
        query = "INSERT INTO %s (%s) VALUES (%s)" % (table, fields, bound_parameters)
        self.cursor.execute(query, values)
        # we need to commit the changes
        self.connection.commit()
        return True

    def rem_record(self, table, field, value):
        """ Remove a record from the database.

        :table (string) -- Where to search for the record.
        :field (string) -- More info on where to search for a specific value.
        :value (string) -- If matched, will serve as an axis for deletion.
        """
        query = 'DELETE FROM %s WHERE %s="%s"' % (str(table), str(field), value)
        self.cursor.execute(query)
        self.connection.commit()
        return True

    def change_record(self, old_object, new_object):
        """ Alter a record present in the database.

        This is a fake way of doing things, being simply
        an alias to a call of "rem_record()" and "add_record()".

        :old_object (object) -- The object to be removed.
        :new_object (object) -- The object to be added.
        """
        self.rem_record(old_object.media_type, 'uri', old_object.uri)
        self.add_record(new_object.media_type, new_object)
        return True

    def get_records(self, table, fields_list):
        """ Retrieves records from the database.

        The method is pretty much self explanatory, but the way it
        returns information is not in bulk. It uses the 'yield'
        keyword, which means that, instead of returning a huge chunk
        of database records, it will return them one by one. Some
        code to exemplify the usage:
        for row in class.get_records("mytable", ["name", "age"]):
            print 'Printing the database records:'
            print row['name']
            print row['age']
            print row['address']
            print row['phone_number']

        :table (string) -- The table where to look for the records.
        :fields_list (list) -- The fields to sort the information by.
        """
        # Construct the query string
        query = "SELECT * FROM %s ORDER BY %s" % (table, ', '.join(fields_list))
        # Execute the query
        self.cursor.execute(query)
        # Get all the column names from the current table
        column_names = []
        for name in self.cursor.description:
            column_names.append(name[0])
        # Create the dictionary that will hold "column_name":"cell_value"
        row = {}
        row.fromkeys(column_names)
        # Populate the dictionary with all the values
        for row_data in self.cursor.fetchall():
            i = 0
            for cell in row_data:
                row[column_names[i]] = cell
                i += 1
            # Yield the dictionary, representing a row of data
            yield row

    def _connect(self, database_name):
        """ Connect to the specified database_name.

        :database_name (string) -- The name of the database to connect to.
        """
        # Create a connection to a specific database (database_name)
        self.connection = pysqlite2.dbapi2.connect(database_name)
        # Get a cursor object that operates in the context of the connection
        self.cursor = self.connection.cursor()

    def _disconnect(self):
        """ Disconnect from the databse.

        In this specific implementation (SQLite), the database doesn't use
        a client/server model, so this method is completely superflues.
        Using Python's reference counting model to clean up, simply stop
        referencing the object to have everything cleaned up. This method
        is only present to remove any doubts that its absence of
        implementation is due to some kind of programmer's flaw.
        """
        pass

    def _rebuild_storage_system(self):
        """Rebuild the whole databse from scratch."""
        query = """CREATE TABLE audio (
            uri TEXT,
            length INTEGER,
            size INTEGER,
            media_type TEXT,
            mime_type TEXT,
            track_number INTEGER,
            title TEXT,
            artist TEXT,
            album TEXT,
            genre TEXT,
            year INTEGER,
            album_art TEXT
        );"""
        self.cursor.execute(query)
        query = """CREATE TABLE video (
            uri TEXT,
            length INTEGER,
            size INTEGER,
            media_type TEXT,
            mime_type TEXT,
            title TEXT
        );"""
        self.cursor.execute(query)
        query = """CREATE TABLE image (
            uri TEXT,
            size INTEGER,
            media_type TEXT,
            mime_type TEXT,
            title TEXT,
            width INTEGER,
            height INTEGER
        );"""
        self.cursor.execute(query)
        query = """CREATE TABLE paths (
            uri TEXT,
            audio TEXT,
            video TEXT,
            image TEXT
        );"""
        self.cursor.execute(query)
